﻿using Dapper;
using Domain.Entity.Basic_information;
using Domain.Entity.Exception;
using Domain.Entity.System_operation;
using Domain.Entity.System_Ordermanagement;
using Domain.IRepository.Exception;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net.NetworkInformation;
using System.Text;
using System.Threading.Tasks;
using Volo.Abp.DependencyInjection;

namespace EFCore.Repository.Exception
{
    [Dependency(ServiceLifetime.Scoped)]
    public class ExceRepository : IExceRepository
    {

        public MyDbContext myDbContext { get; set; }

        public ExceRepository(MyDbContext myDbContext)
        {
            this.myDbContext = myDbContext;
        }

        public List<Exception_log> GetExce(int status, int state, string event1, string event2, string m_time1, string m_time2, string cname)
        {
            string sql = $@"SELECT E.* FROM dbo.Exception_log E
            INNER JOIN dbo.Wb_meter W ON E.Wb_id=W.Wb_id
            INNER JOIN dbo.Orders O ON O.Ord_id=W.Ord_id
            WHERE Is_del=0 ";
            if(status != 0)
            {
                sql += $" AND Mea_status={status}";
            }
            if(state != 0)
            {
                sql += $" AND Ord_state={state}";
            }
            if(event1 != null && event2 != null)
            {
                sql += $" AND (Event_time >= '{event1}' AND E.Event_time <= '{event2}')";
            }
            if(m_time1 != null && m_time2 != null)
            {
                sql += $" AND (Mea_time >= '{m_time1}' AND E.Mea_time <= '{m_time2}')";
            }
            if(cname != null)
            {
                sql += $" AND Client_name LIKE '%{cname}%'";
            }
            sql += " ORDER BY Exc_id DESC";
            return myDbContext.Database.GetDbConnection().Query<Exception_log>(sql).ToList();
        }

        public List<Wb_meter> Getwb_Meters(int status, int state, string event1, string event2, string m_time1, string m_time2, string cname)
        {
            string sql = $@"SELECT W.* FROM dbo.Exception_log E
            INNER JOIN dbo.Wb_meter W ON E.Wb_id=W.Wb_id
            INNER JOIN dbo.Orders O ON O.Ord_id=W.Ord_id
            WHERE Is_del=0 ";
            if (status != 0)
            {
                sql += $" AND Mea_status={status}";
            }
            if (state != 0)
            {
                sql += $" AND Ord_state={state}";
            }
            if (event1 != null && event2 != null)
            {
                sql += $" AND (Event_time >= '{event1}' AND E.Event_time <= '{event2}')";
            }
            if (m_time1 != null && m_time2 != null)
            {
                sql += $" AND (Mea_time >= '{m_time1}' AND E.Mea_time <= '{m_time2}')";
            }
            if (cname != null)
            {
                sql += $" AND Client_name LIKE '%{cname}%'";
            }
            sql += " ORDER BY Exc_id DESC";
            return myDbContext.Database.GetDbConnection().Query<Wb_meter>(sql).ToList();
        }

        public List<Orders> GetOrders(int status, int state, string event1, string event2, string m_time1, string m_time2, string cname)
        {
            string sql = $@"SELECT O.Ord_state, O.Client_name FROM dbo.Exception_log E
            INNER JOIN dbo.Wb_meter W ON E.Wb_id=W.Wb_id
            INNER JOIN dbo.Orders O ON O.Ord_id=W.Ord_id
            WHERE Is_del=0 ";
            if (status != 0)
            {
                sql += $" AND Mea_status={status}";
            }
            if (state != 0)
            {
                sql += $" AND Ord_state={state}";
            }
            if (event1 != null && event2 != null)
            {
                sql += $" AND (Event_time >= '{event1}' AND E.Event_time <= '{event2}')";
            }
            if (m_time1 != null && m_time2 != null)
            {
                sql += $" AND (Mea_time >= '{m_time1}' AND E.Mea_time <= '{m_time2}')";
            }
            if (cname != null)
            {
                sql += $" AND Client_name LIKE '%{cname}%'";
            }
            sql += " ORDER BY Exc_id DESC";
            return myDbContext.Database.GetDbConnection().Query<Orders>(sql).ToList();
        }

        public List<Drivers_ment> GetDrivers(int status, int state, string event1, string event2, string m_time1, string m_time2, string cname)
        {
            string sql = $@"SELECT D.Dv_name, D.Dv_phone FROM dbo.Exception_log E
            INNER JOIN dbo.Wb_meter W ON E.Wb_id=W.Wb_id
            INNER JOIN dbo.Orders O ON O.Ord_id=W.Ord_id
            INNER JOIN dbo.Drivers_ment D ON D.Dv_id=W.Dv_id
            WHERE E.Is_del=0 ";
            if (status != 0)
            {
                sql += $" AND Mea_status={status}";
            }
            if (state != 0)
            {
                sql += $" AND Ord_state={state}";
            }
            if (event1 != null && event2 != null)
            {
                sql += $" AND (Event_time >= '{event1}' AND E.Event_time <= '{event2}')";
            }
            if (m_time1 != null && m_time2 != null)
            {
                sql += $" AND (Mea_time >= '{m_time1}' AND E.Mea_time <= '{m_time2}')";
            }
            if (cname != null)
            {
                sql += $" AND Client_name LIKE '%{cname}%'";
            }
            sql += " ORDER BY Exc_id DESC";
            return myDbContext.Database.GetDbConnection().Query<Drivers_ment>(sql).ToList();
        }

        public List<Vehicles_ment> GetVehicles(int status, int state, string event1, string event2, string m_time1, string m_time2, string cname)
        {
            string sql = $@"SELECT V.Vc_phone FROM dbo.Exception_log E
            INNER JOIN dbo.Wb_meter W ON E.Wb_id=W.Wb_id
            INNER JOIN dbo.Orders O ON O.Ord_id=W.Ord_id
            INNER JOIN dbo.Drivers_ment D ON D.Dv_id=W.Dv_id
            INNER JOIN dbo.Vehicles_ment V ON V.Vc_id=W.Vc_id
            WHERE E.Is_del=0 ";
            if (status != 0)
            {
                sql += $" AND Mea_status={status}";
            }
            if (state != 0)
            {
                sql += $" AND Ord_state={state}";
            }
            if (event1 != null && event2 != null)
            {
                sql += $" AND (Event_time >= '{event1}' AND E.Event_time <= '{event2}')";
            }
            if (m_time1 != null && m_time2 != null)
            {
                sql += $" AND (Mea_time >= '{m_time1}' AND E.Mea_time <= '{m_time2}')";
            }
            if (cname != null)
            {
                sql += $" AND Client_name LIKE '%{cname}%'";
            }
            sql += " ORDER BY Exc_id DESC";
            return myDbContext.Database.GetDbConnection().Query<Vehicles_ment>(sql).ToList();
        }

        public List<Carrier_ment> GetCarriers(int status, int state, string event1, string event2, string m_time1, string m_time2, string cname)
        {
            string sql = $@"SELECT C.Cr_cp_name FROM dbo.Exception_log E
            INNER JOIN dbo.Wb_meter W ON E.Wb_id=W.Wb_id
            INNER JOIN dbo.Orders O ON O.Ord_id=W.Ord_id
            INNER JOIN dbo.Carrier_ment C ON C.Cr_id=W.Cr_id
            WHERE E.Is_del=0 ";
            if (status != 0)
            {
                sql += $" AND Mea_status={status}";
            }
            if (state != 0)
            {
                sql += $" AND Ord_state={state}";
            }
            if (event1 != null && event2 != null)
            {
                sql += $" AND (Event_time >= '{event1}' AND E.Event_time <= '{event2}')";
            }
            if (m_time1 != null && m_time2 != null)
            {
                sql += $" AND (Mea_time >= '{m_time1}' AND E.Mea_time <= '{m_time2}')";
            }
            if (cname != null)
            {
                sql += $" AND Client_name LIKE '%{cname}%'";
            }
            sql += " ORDER BY Exc_id DESC";
            return myDbContext.Database.GetDbConnection().Query<Carrier_ment>(sql).ToList();
        }

        public int GetCount(int status, int state, string event1, string event2, string m_time1, string m_time2, string cname)
        {
            string sql = $@"SELECT E.* FROM dbo.Exception_log E
            INNER JOIN dbo.Wb_meter W ON E.Wb_id=W.Wb_id
            INNER JOIN dbo.Orders O ON O.Ord_id=W.Ord_id
            WHERE E.Is_del=0 ";
            if (status != 0)
            {
                sql += $" AND Mea_status={status}";
            }
            if (state != 0)
            {
                sql += $" AND Ord_state={state}";
            }
            if (event1 != null && event2 != null)
            {
                sql += $" AND (Event_time >= ' {event1} ' AND E.Event_time <= '{event2}')";
            }
            if (m_time1 != null && m_time2 != null)
            {
                sql += $" AND (Mea_time >= '{m_time1}' AND E.Mea_time <= '{m_time2}')";
            }
            if (cname != null)
            {
                sql += $" AND Client_name LIKE '%{cname}%'";
            }
            sql += " ORDER BY Exc_id DESC";
            return myDbContext.Database.GetDbConnection().Query<Exception_log>(sql).Count();
        }

        public List<Wb_meter> GetWb_id()
        {
            string sql = "SELECT * FROM Wb_meter";
            return myDbContext.Database.GetDbConnection().Query<Wb_meter>(sql).ToList();
        }

        public int AddExce(Exception_log exception)
        {
            string sql = $@"INSERT INTO Exception_log(Wb_id, Exc_number, Event_type, Event_time, Event_place, Event_plain, Mea_status, Report_by, Report_time, Is_del)
VALUES({exception.Wb_id}, '{exception.Exc_number}', {exception.Event_type}, '{exception.Event_time}', '{exception.Event_place}', '{exception.Event_plain}', 55, '{exception.Report_by}', '{exception.Report_time}', 0)";
            return myDbContext.Database.ExecuteSqlRaw(sql);
        }

        public Exception_log GetException(int id)
        {
            string sql = $"SELECT * FROM Exception_log WHERE Is_del=0 AND Exc_id={id}";
            return myDbContext.Database.GetDbConnection().Query<Exception_log>(sql).FirstOrDefault();
        }

        public int EditExce(Exception_log exception)

        {
            string sql = $@"UPDATE dbo.Exception_log SET Wb_id={exception.Wb_id}, Event_type='{exception.Event_type}', Event_time='{exception.Event_time}', Event_place='{exception.Event_place}', Event_plain='{exception.Event_plain}',  Update_by='{exception.Update_by}', Update_time='{exception.Update_time}'
WHERE Exc_id={exception.Exc_id}";
            return myDbContext.Database.ExecuteSqlRaw(sql);
        }

        public int ClExce(Exception_log exception)
        {
            string sql = $@"UPDATE dbo.Exception_log SET Event_cause='{exception.Event_cause}', Event_effect='{exception.Event_effect}', Mea_status={exception.Mea_status}, Mea_by='{exception.Mea_by}', Mea_time='{exception.Mea_time}', Measures='{exception.Measures}'
WHERE Exc_id={exception.Exc_id}";
            return myDbContext.Database.ExecuteSqlRaw(sql);
        }

        public int WcExce(int id)
        {
            string sql = $"UPDATE Exception_log SET Mea_status=57 WHERE Exc_id={id}";
            return myDbContext.Database.ExecuteSqlRaw(sql);
        }

        public int DelExce(int id)
        {
            string sql = $"UPDATE Exception_log SET Is_del=1 WHERE Exc_id={id}";
            return myDbContext.Database.ExecuteSqlRaw(sql);    
        }

    }
}
